Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Reduce Unnecessary Memory Usage

Because memory is usually a critical resource, anything you can do to reduce the amount of memory allocated to non-Oracle processes helps free memory that can be used for additional SGA memory or server processes. One way to reduce unnecessary memory is to reduce the size of the disk cache.

Because Oracle provides for its own disk cache (in the form of the SGA), it is unnecessary to provide large amounts of memory for OS disk caching. You can reduce the amount of OS disk caching by adjusting the value of the cache parameter for the IFS line in CONFIG.SYS for HPFS or HPFS386. If you are using FAT, there is a separate line, DISKCACHE. This value should be reduced, but probably to less than 2048. A reasonable value for both these values is 2048.

SGA Tuning

To maximize performance, allocate as much memory as possible to the SGA. Use the techniques discussed in Chapters 9 and 10 to determine whether the memory can best be used for the shared pool or for database block buffers.

Although it is possible in a virtual memory system to allocate more memory for the SGA than there is physical memory, you should avoid this practice. The overhead of paging and swapping outweighs any benefits you may see from a larger SGA.

Remember that user processes also require large amounts of memory. You should frequently monitor your system to make sure that no paging is occurring at any time.

User Capacity

The method used to determine the per-user memory requirements for OS/2 is similar to the method used for Windows NT: Start up Oracle and record the amount of available memory by using the OS/2 performance monitor or the SMP performance monitor. Record the amount of memory being used. Once a specified number of users begin to access the application, record the amount of memory again. Take the difference between these values and divide the result by the number of users accessing the application. This value is the per-user memory usage. Multiply this value by the maximum number of users who may connect to the application to determine the amount of memory you must reserve for user connections. Be sure to add a little extra memory just in case.

Unlike NetWare, the size of a user’s PGA in OS/2 is not bound by any initialization parameters. Because of this, be careful that a user’s PGA does not consume too much system memory.

To increase the number of Oracle connections, you can adjust the Oracle initialization parameter PROCESSES. The PROCESSES parameter should reflect the maximum number of user connections you expect to have plus the Oracle background processes. You should also include some extra processes for administrative tasks.

Network

With OS/2 as with Windows NT, it is unnecessary to adjust any networking parameters. The OS/2 system is designed to be accessed through a network and does not require any tuning.

I/O Subsystem

As is true for all operating systems, it is very important to ensure that performance is not bound by physical I/O rates. Be sure that random I/Os are not exceeding the physical limitations of the disk drives. Refer to Chapters 14 and 15 for more details.

With OS/2, as with Windows NT and NetWare, Asynchronous I/O (AIO) is always enabled. There is no need to adjust any tuning parameters to ensure that AIO is enabled.

The default block size for Oracle on OS/2 is 2048. This may be sufficient. If you have a very large database or the data access is primarily sequential, you may want to increase DB_BUFFER_SIZE to 4096. Although the blocks take up more space in the SGA, the number of I/Os is significantly reduced. It doesn’t take very much more overhead and time to retrieve 4K of data from the disk than it does to get 2K of data.

If your data access is primarily sequential, you may benefit by setting DB_BLOCK_SIZE to 8192. Because sequential accesses to data definition uses the next rows in the database, when you read more of those rows with a larger block size, the rows are already in the SGA when you are ready to use them. This prefetching cuts down on I/O overhead. If you are accessing the database in a random fashion, prefetching ends up wasting space in the SGA. The value you choose for the block size affects performance, either for the better or the worse. If you are unsure, leave the parameter at the default of 2048.

When you are creating OS/2 file systems, you have three main choices:

  FAT
  HPFS, sometimes known as HPFS286
  HPFS386

Of the these three choices for file systems, I generally recommend HPFS386. Although there are certain conditions under which FAT performs better, in general, you will see the best performance from HPFS386. Under most situations, especially under Oracle, HPFS386 provides the best performance and flexibility

OS/2 Summary

OS/2, like Windows NT, is designed to run applications out of the box with little or no system tuning. As such, most of the areas of concern in an OS/2 environment focus around resource allocation rather than on tuning. The primary areas of concern are reducing OS overhead and making sure that your system is not paging. With OS/2, as with NetWare SMP, Windows NT, and UNIX, you can get scaleable performance by adding additional CPUs.

The preceding sections described a few parameters you can adjust in the OS/2 operating system as well as in Oracle itself. The most important area of tuning in OS/2 is the amount of memory that can be dedicated to Oracle. You learned about several ways to maximize the memory available to Oracle by reducing some of OS/2’s overhead. You also learned about some slight performance gains you can obtain by carefully choosing your file system. Finally, you learned how to maximize I/O usage by carefully choosing the correct block size.

Use these tuning guidelines in conjunction with other guidelines described in this book to maximize the performance of the Oracle server. Tuning the server operating system in conjunction with the application and client provides for the best-performing system possible.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.